const { execSQL } = require('../db/mysql');

// 单词相关的方法

// 获取单词
const getVocabulary = (id, keyword) => {
    let sql = `select * from VOCABULARY where `;
    if (id) {
        sql += `id='${id}'`;
    }
    if (keyword) {
        sql += `spelling like '%${keyword}%'`;
    }
    return execSQL(sql);
}

// 获取章节单词列表
const getChapterWordList = (chapter_id) => {
    // 优化SQL语句，直接查询
    let sql = `select BK_VOC.*,VOCABULARY.spelling from BK_VOC,VOCABULARY where chapter_id=${chapter_id} and BK_VOC.voc_id = VOCABULARY.id`;
    return execSQL(sql);

    // 不更改SQL语句，时间长
    // let sql = `select * from BK_VOC where chapter_id=${chapter_id}`;
    // const data = await execSQL(sql);
    // for (const iterator of data) {
    //     await getVocabulary(iterator['voc_id']).then((voc) => {
    //         iterator['voc'] = voc[0]['spelling'];
    //     });
    // }
    // return await data;
}

// 获取单词例句
const getVOCPhrase = (voc_id) => {
    let sql = `select * from VOC_PHRASE where voc_id=${voc_id}`;
    return execSQL(sql);
}

// 添加单词例句
const newVOCPhrase = async (data) => {
    let sql = `insert into VOC_PHRASE(voc_id,phrase,interpretation,creator) values('${data.voc_id}','${data.phrase}','${data.interpretation}','${data.creator}')`;
    const result = await execSQL(sql);
    return result.insertId;
}

module.exports = {
    getVocabulary,
    getChapterWordList,
    getVOCPhrase,
    newVOCPhrase,
}
